MySQL update语句加锁分析 |
您所在的位置：网站首页 › MySQL update会加锁吗 › MySQL update语句加锁分析 |
|
1. 通过二级唯一索引更新聚簇索引
表结构如下： CREATE TABLE `test_lock_cluster` ( `id` int NOT NULL, `age` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `age` (`age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 无主键冲突 插入一条记录： insert into test_lock_cluster values(2, 3);接着开启事务，执行更新操作 begin; update test_lock_cluster set id=id+1 where age=3;此时会加什么锁呢？容易想到的是where条件中的二级唯一索引加X锁，即age=3这条记录加上X锁，并且对应的聚簇索引也加X锁，同时，RR隔离级别下为避免幻读，会加GAP锁，那么GAP锁究竟会加在哪里呢？ 首先，使用show engine innodb status;看一下innodb的状态（已去掉跟锁无关的日志）： ---TRANSACTION 3113, ACTIVE 73 sec 5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2 MySQL thread id 11, OS thread handle 123145555865600, query id 82 localhost root TABLE LOCK table `dian_test`.`test_lock_cluster` trx id 3113 lock mode IX # lock mode IX: 表级意向写锁 # 下面一句表示在二级唯一索引上加了X锁 RECORD LOCKS space id 3 page no 5 n bits 72 index age of table `dian_test`.`test_lock_cluster` trx id 3113 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000003; asc ;; 1: len 4; hex 80000002; asc ;; # 聚簇索引上加了X锁 RECORD LOCKS space id 3 page no 4 n bits 72 index PRIMARY of table `dian_test`.`test_lock_cluster` trx id 3113 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000c29; asc );; 2: len 7; hex 020000010f050f; asc ;; 3: len 4; hex 80000003; asc ;; # 以下几行表明在二级唯一索引上加了2个GAP锁 RECORD LOCKS space id 3 page no 5 n bits 72 index age of table `dian_test`.`test_lock_cluster` trx id 3113 lock mode S locks gap before rec Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 4; hex 80000003; asc ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000003; asc ;; 1: len 4; hex 80000002; asc ;; # 在上确界(supremum)加了读GAP锁，注意这里的lock mode S并不是next-key锁，因为heap no 1说明锁是加在supremum上，属于GAP锁 RECORD LOCKS space id 3 page no 5 n bits 72 index age of table `dian_test`.`test_lock_cluster` trx id 3113 lock mode S Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;以上日志说明总共加了6把锁，1个表锁(IX)和5个记录锁(2个X锁+3个GAP锁)，除了二级索引上确界的那把GAP锁，另外的两个GAP锁加的地方可以进一步从performance_schema.data_locks表里查看更多信息： select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140428987080008:1062:140429194506000 ENGINE_TRANSACTION_ID: 3113 THREAD_ID: 51 EVENT_ID: 59 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140429194506000 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED #表级意向写锁 LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140428987080008:3:5:3:140429198791712 ENGINE_TRANSACTION_ID: 3113 THREAD_ID: 51 EVENT_ID: 59 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: age OBJECT_INSTANCE_BEGIN: 140429198791712 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 3, 2 # age=3的二级唯一索引上加X锁 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140428987080008:3:4:3:140429198792056 ENGINE_TRANSACTION_ID: 3113 THREAD_ID: 51 EVENT_ID: 59 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140429198792056 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 # id=2的聚簇索引上加X锁 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140428987080008:3:5:2:140429198792400 ENGINE_TRANSACTION_ID: 3113 THREAD_ID: 51 EVENT_ID: 59 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: age OBJECT_INSTANCE_BEGIN: 140429198792400 LOCK_TYPE: RECORD LOCK_MODE: S,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3, 3 # 修改后age=3的二级唯一索引之前加读GAP锁 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140428987080008:3:5:3:140429198792400 ENGINE_TRANSACTION_ID: 3113 THREAD_ID: 51 EVENT_ID: 59 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: age OBJECT_INSTANCE_BEGIN: 140429198792400 LOCK_TYPE: RECORD LOCK_MODE: S,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3, 2 # 修改前age=3的二级唯一索引之前加读GAP锁 *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140428987080008:3:5:1:140429198792744 ENGINE_TRANSACTION_ID: 3113 THREAD_ID: 51 EVENT_ID: 59 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: age OBJECT_INSTANCE_BEGIN: 140429198792744 LOCK_TYPE: RECORD LOCK_MODE: S LOCK_STATUS: GRANTED LOCK_DATA: supremum pseudo-record # 上确界加读GAP锁上面是增加主键的值所以在上确界加了GAP锁，如果是减少主键的值，就会在下确界加GAP锁，其他的锁基本相同。 总结起来，通过二级唯一索引更新聚簇索引时，总共会加6把锁，分别是：表级意向写锁(IX)、二级唯一索引上的X锁(age=3)、聚簇索引上的X锁(id=2)、二级索引上确界的读GAP锁(age>3)、二级索引更新之前记录前的读GAP锁(age=3,id=2)、二级索引更新之后记录前的读GAP锁(age=3,id=3). 主键冲突时的加锁情况 mysql> select * from test_lock_cluster; +----+------+ | id | age | +----+------+ | 1 | 4 | | 2 | 5 | | 9 | 10 | | 10 | 11 | | 11 | 12 | | 12 | 13 | | 13 | 14 | | 15 | 15 | +----+------+ mysql> update test_lock_cluster set id=id-1 where age=14; ERROR 1062 (23000): Duplicate entry '12' for key 'test_lock_cluster.PRIMARY'show engine innodb status;的结果： 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 11, OS thread handle 123145349824512, query id 80 localhost root TABLE LOCK table `dian_test`.`test_lock_cluster` trx id 9761 lock mode IX RECORD LOCKS space id 3 page no 5 n bits 80 index age of table `dian_test`.`test_lock_cluster` trx id 9761 lock_mode X locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000e; asc ;; 1: len 4; hex 8000000d; asc ;; RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `dian_test`.`test_lock_cluster` trx id 9761 lock_mode X locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000000d; asc ;; 1: len 6; hex 00000000261f; asc & ;; 2: len 7; hex 81000001070110; asc ;; 3: len 4; hex 8000000e; asc ;; RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `dian_test`.`test_lock_cluster` trx id 9761 lock mode S locks rec but not gap Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000000c; asc ;; 1: len 6; hex 000000002619; asc & ;; 2: len 7; hex 82000001080110; asc ;; 3: len 4; hex 8000000d; asc ;;select * from performance_schema.data_locks\G的结果 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:1062:140459838089152 ENGINE_TRANSACTION_ID: 9761 THREAD_ID: 52 EVENT_ID: 33 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140459838089152 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:3:5:7:140459842314272 ENGINE_TRANSACTION_ID: 9761 THREAD_ID: 52 EVENT_ID: 33 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: age OBJECT_INSTANCE_BEGIN: 140459842314272 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 14, 13 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:3:4:7:140459842314616 ENGINE_TRANSACTION_ID: 9761 THREAD_ID: 52 EVENT_ID: 33 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140459842314616 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 13 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:3:4:6:140459842314960 ENGINE_TRANSACTION_ID: 9761 THREAD_ID: 52 EVENT_ID: 33 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140459842314960 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 12 4 rows in set (0.00 sec)通过二级索引更新主键产生主键冲突时，会加4把锁，分别是表级意向写锁、二级索引上的记录X锁、更新前主键上的记录X锁、产生冲突的主键记录X锁。 2. 通过聚簇索引更新二级唯一索引 二级唯一索引无冲突的情况 mysql> select * from test_lock_cluster; +----+------+ | id | age | +----+------+ | 2 | 3 | +----+------+ begin; Query OK, 0 rows affected (0.00 sec) mysql> update test_lock_cluster set age=age+1 where id=2; --------------------------------------------------------------- mysql> show engine innodb status; ---TRANSACTION 4103, ACTIVE 3 sec 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 16, OS thread handle 123145454256128, query id 22 localhost root --------------------------------------------------------------- # 查加锁情况 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140274368257352:1062:140274638573920 ENGINE_TRANSACTION_ID: 4103 THREAD_ID: 57 EVENT_ID: 20 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140274638573920 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140274368257352:3:4:3:140274630211616 ENGINE_TRANSACTION_ID: 4103 THREAD_ID: 57 EVENT_ID: 20 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140274630211616 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 2 rows in set (0.00 sec)二级唯一索引无冲突时，只加了意向写锁(IX)和聚簇索引上的X锁。 二级唯一索引有冲突的情况 mysql> insert into test_lock_cluster values(3,5); Query OK, 1 row affected (0.00 sec) mysql> select * from test_lock_cluster; +----+------+ | id | age | +----+------+ | 2 | 4 | | 3 | 5 | +----+------+ 2 rows in set (0.00 sec) #开启事务，执行updaet mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test_lock_cluster set age=age+1 where id=2; --------------------------------------------------------------- ---TRANSACTION 4110, ACTIVE 4 sec 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 16, OS thread handle 123145454256128, query id 33 localhost root --------------------------------------------------------------- # 查询加锁情况 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140274368257352:1062:140274638573920 ENGINE_TRANSACTION_ID: 4110 THREAD_ID: 57 EVENT_ID: 31 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140274638573920 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140274368257352:3:4:3:140274630211616 ENGINE_TRANSACTION_ID: 4110 THREAD_ID: 57 EVENT_ID: 31 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140274630211616 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140274368257352:3:5:3:140274630211960 ENGINE_TRANSACTION_ID: 4110 THREAD_ID: 57 EVENT_ID: 31 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: age OBJECT_INSTANCE_BEGIN: 140274630211960 LOCK_TYPE: RECORD LOCK_MODE: S LOCK_STATUS: GRANTED LOCK_DATA: 5, 3 3 rows in set (0.01 sec)发生唯一键冲突时，除了在聚簇索引加X锁，冲突的二级索引上也加了S锁。 3. 通过主键更新二级非唯一索引字段 test_lock_cluster | CREATE TABLE `test_lock_cluster` ( `id` int NOT NULL, `age` int DEFAULT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `age` (`age`), KEY `idx_name` (`name`) ) ENGINE=InnoDB; # 开启事务，执行更新语句 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test_lock_cluster set name='aa' where id=16; Query OK, 1 row affected (0.00 sec) # 查询加锁情况 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:1062:140459838089152 ENGINE_TRANSACTION_ID: 9782 THREAD_ID: 52 EVENT_ID: 55 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140459838089152 LOCK_TYPE: TABLE LOCK_MODE: IX //表级意向读锁 LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:3:4:12:140459842314272 ENGINE_TRANSACTION_ID: 9782 THREAD_ID: 52 EVENT_ID: 55 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140459842314272 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP //主键记录X锁 LOCK_STATUS: GRANTED LOCK_DATA: 16 2 rows in set (0.00 sec)加锁分析：总共两把锁，分别是表级意向写锁和聚簇索引记录X锁 4. 通过唯一索引更新二级非唯一索引字段表结构同上。 mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> update test_lock_cluster set name='ab' where age=16; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:1062:140459838089152 ENGINE_TRANSACTION_ID: 9788 THREAD_ID: 52 EVENT_ID: 62 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140459838089152 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:3:5:8:140459842314272 ENGINE_TRANSACTION_ID: 9788 THREAD_ID: 52 EVENT_ID: 62 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: age OBJECT_INSTANCE_BEGIN: 140459842314272 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 16, 16 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:3:4:12:140459842314616 ENGINE_TRANSACTION_ID: 9788 THREAD_ID: 52 EVENT_ID: 62 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140459842314616 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 16 3 rows in set (0.00 sec)加锁分析：总共3把锁，表级意向写锁，唯一索引上的记录锁，以及聚集索引上的记录锁。 5. 通过主键更新非索引字段 CREATE TABLE `test_lock_cluster` ( `id` int NOT NULL, `age` int DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `addr` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `age` (`age`), KEY `idx_name` (`name`) ) ENGINE=InnoDB # 开启事务 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test_lock_cluster set addr='hubei' where id=17; Query OK, 1 row affected (0.00 sec) # 查询加锁情况 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:1062:140459838089152 ENGINE_TRANSACTION_ID: 9803 THREAD_ID: 52 EVENT_ID: 76 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140459838089152 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:3:4:13:140459842314272 ENGINE_TRANSACTION_ID: 9803 THREAD_ID: 52 EVENT_ID: 76 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140459842314272 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 17 2 rows in set (0.00 sec)加锁分析：总共2把锁，表级意向写锁，以及聚集索引上的x锁。 6. 通过唯一索引更新非索引字段 # 查询加锁情况（省略了SQL语句） mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:1062:140459838089152 ENGINE_TRANSACTION_ID: 9805 THREAD_ID: 52 EVENT_ID: 82 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140459838089152 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:3:5:11:140459842314272 ENGINE_TRANSACTION_ID: 9805 THREAD_ID: 52 EVENT_ID: 82 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: age OBJECT_INSTANCE_BEGIN: 140459842314272 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 17, 17 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140459623517512:3:4:13:140459842314616 ENGINE_TRANSACTION_ID: 9805 THREAD_ID: 52 EVENT_ID: 82 OBJECT_SCHEMA: dian_test OBJECT_NAME: test_lock_cluster PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140459842314616 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 17 3 rows in set (0.00 sec)加锁分析：总共3把锁，表级意向写锁，唯一索引上的记录锁，以及聚集索引上加记录锁。 7. 总结以上总共列出了6大类更新语句的加锁情况，可以看出，每种更新都会加表级意向写锁，除此之外，还会有记录锁、GAP锁等出席，这里做一下总结（不再列出IX锁）： 通过二级唯一索引更新聚簇索引： 无主键冲突时，总共会加5把锁，分别是：二级唯一索引上的X锁、聚簇索引上的X锁、二级索引上确界的读GAP锁、二级索引更新之前记录前的读GAP锁、二级索引更新之后记录前的读GAP锁 主键冲突时，会加3把锁，二级索引上的记录X锁、更新前主键上的记录X锁、产生冲突的主键记录X锁。 通过主键更新唯一索引时 无索引冲突，加聚簇索引上的X锁。 索引冲突时，表级聚簇索引加X锁，冲突的二级索引上也加了S锁 通过主键更新二级非唯一索引字段，聚簇索引记录X锁 通过唯一索引更新二级非唯一索引字段，唯一索引上以及聚集索引上加记录锁 通过主键更新非索引字段, 聚集索引上加x锁 通过唯一索引更新非索引字段，唯一索引上的记录锁，以及聚集索引上加记录锁 8. 参考资料 解决死锁之路（终结篇） - 再见死锁 10分钟让你明白MySQL是如何利用索引的 InnoDB 锁的类型 解决死锁之路 - 常见 SQL 语句的加锁分析 何登成的技术博客 » MySQL 加锁处理分析 MySQL · 引擎特性 · InnoDB 事务锁系统简介 8.2.1.5 Index Condition Pushdown Optimization 何登成的技术博客 » SQL中的where条件，在数据库中提取与应用浅析 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |